• Understand basics of data models
• Learn how to design and create effective data tables

Data are heterogeneous in:

• Structure (schema): Logical model of the data (e.g., tables, hierarchical trees, raster images, etc.)
• Semantics: Specific meaning of the data (e.g., nature and types of measurements, importance of contextual information, interpretation of record structure, etc.): documentation
• Syntax: Digital format of the data (e.g., csv, “R dataframe”, NetCDF, Excel XLSX, DBF, etc.)

Spreadsheets are (still) the primary data entry tool of the digital age!

the Good:

• Quick on the draw (clickety-click and you’re ready for action)
• Always there (on most everyone’s computer)
• Smarter than he lets on (stats, pivot tables, VB scripts)
• Cleans up real pretty (graphics, fonts, colors, borders)

the Bad:

• Also a fast shooter (click&fire)
• No scruples (delete row, click, ctrl-x/ctrl-c, re-sort, save)
• Talks a good story, but unreliable (e.g. http://www.practicalstats.com/xlsstats/excelstats.html)

the Ugly:

• Ill-mannered: takes your data prisoner; conflates raw data with summary data
• Gaudy: Use of visual formatting to indicate critical metadata or other semantic tidbits
• Shifty: Cross-linking of worksheets sets up “invisible” dependencies
• The more complicated your Spreadsheet, the UGLIER it gets in terms of using it with other software

=> Encourages you to mix your data and your analaysis

=> A Spreadsheet is not a table !!

Table = Relation = Data set (~ Worksheet)

Column = Variable = Attribute = Characteristic

Row = Record = Tuple <> Observation

Keys are used to Join or Merge

Cell = Value = Measurement

Data Model = Schema

Observations about different entities combined

Observations. A better way to model data is to organize the observations about each type of entity in its own table. This results in:

• Separate tables for each type of entity measured
• Each row represents a single observed entity
• Observations (rows) are all unique

This is normalized data (aka tidy data)

Variables. In addition, for normalized data, we expect the variables to be organized such that:

• All values in a column are of the same type
• All columns pertain to the same observed entity (e.g., row)

When one has normalized data, we often use unique identifiers to reference particular observations, which allows us to link across tables. Two types of identifiers are common within relational data:

• Primary Key: unique identifier for each observed entity, one per row
• Foreign Key: reference to a primary key in another table (linkage)

An Entity-Relationship model allows us to compactly draw the structure of the tables in a relational database, including the primary and foreign keys in the tables.

In the above model, one can see that each site in the SITES table must have one or more observations in the PLOTOBS table, whereas each PLOTOBS has one and only one SITE.

• Design to add rows, not columns
• Each column one type
• Header line
• Nonproprietary formats
• Descriptive names
• No spaces

• Column headers:
• Avoid cryptic names
• Concise, but not meaningful
• Units (kg or g?)
• Color coding:
• avoid using formatting (implicit)
• add a column to store this information with a flag

• Borer et al. 2009. Some Simple Guidelines for Effective Data Management. Bulletin of the Ecological Society of America.
• Software Carpentry SQL tutorial
• Tidy Data